<?php
// +----------------------------------------------------------------------
// | Description: 客户
// +----------------------------------------------------------------------
// | Author:  Zl
// +----------------------------------------------------------------------
namespace app\crm\model;

use app\admin\model\User;
use app\common\traits\ActionRecordTrait;
use think\facade\Db;
use app\admin\model\Common;
use think\response\Json;
use think\Validate;
use app\crm\logic\WhereLogic;

class StatisticHuiyuanTjModel extends Common
{
    use ActionRecordTrait;
    /**
     * 为了数据库的整洁，同时又不影响Model和Controller的名称
     * 我们约定每个模块的数据表都加上相同的前缀，比如CRM模块用crm作为数据表前缀
     */
    protected $name = 'customer';

    /**
     * 列表
     * 会员在门诊和门店的消费合并，达到会员条件时，成为会员
     * 拥有数据权限:
     * 1. 门店看自己
     * 2. 后台看列表
     * @param  array $request 请求参数
     * --------------------------------------------------------------------------+
     * 目前存在的搜索条件
     * -+
     * $request                     搜索参数集
     * --+                          
     * page                      int       页码
     * limit                     int       每页的数据
     * create_time               string    客户录入的日期: ****-**-**
     * customer_name             string    客户的名称
     * sex                       int       性别: 0未知 1男 2女
     * source_id                 int       客户来源
     * effective_entry           int       进店标识: 0未知 1是 2否
     * membership_leve           int       会员等级
     * member_classification     int       客户分类
     * repair_progress           int       修复进展: 0未知 其他设置按照id
     * scar_id                   int       疤痕类型: 关联查询 -- customer_scar表
     * consumption               decimal   消费金额
     * consumption_type          int       搜索消费金额的范围: 1大于 2小于 3等于
     * age                       int       客户的年龄
     * --------------------------------------------------------------------------+
     * @return array
     */
    public function getDataList(array $request): array
    {
        $sTime = strtotime('2022-01-01');

        // echo $sTime;
        // exit;

        // $request = Request::param();

        $start_time = $request['start_time'] ?? '';

        $end_time = $request['end_time'] ?? '';

        // exit;
        // 读取基础数据

        $year = date('Y');

        $moneth = date('m');

        $changdu    = date('t', strtotime($year."-".$moneth."-1"));

        if($start_time){

            $start_time = strtotime($start_time);

        }else{

            $start_time = strtotime($year."-".$moneth."-1");

        }

        if($end_time){

            $end_time = strtotime($end_time);

        }else{

            $end_time = strtotime($year."-".$moneth."-".$changdu);

        }

        // $start_time = mktime(0,0,0,4,1,2022);
        // $end_time = mktime(23,59,59,4,30,2022);

        $where = [];

        // 获取门店数据

        // $shopIds = $this->getCommonShopIdsCollectByUserType();

        // array_push($where,['shop_id','in',$shopIds]);
        array_push($where,['type','=','1']);
        array_push($where,['shop_name','not in','营销中心']);
        array_push($where,['status','=','1']);
        array_push($where,['shop_type','=','2']);

        $shop_list = DB::name('shop')->where($where)->column('shop_id','id');
        

        // 市场
        $market_names = Db::name('shop')->where([['shop_id', 'in', $shop_list]])->column('market_name','shop_id');


        // 门店
        $shop_names = Db::name('shop')->where([['shop_id', 'in', $shop_list]])->column('shop_name','shop_id');

        // 达到条件顾客数
        $cj_list=Db::name('customer')->where([['shop_id', 'in', $shop_list], ['member_classification', 'in', [71,72,73]],['membership_leve','=',0],['final_consumption','between',"$start_time,$end_time"], ['consumption', '>=', '5000'], ['create_time','>=',$sTime]])->field('count(id) as sum,shop_id')->group('shop_id')->select()->toArray();

        $cj_num=array_column($cj_list,null,'shop_id');

        

        // 新增会员顾客数
        $xz_huiyuan_list=Db::name('customer')->where([['shop_id', 'in', $shop_list], ['join_time', 'between', "$start_time,$end_time"]])->field('count(id) as sum,shop_id')->group('shop_id')->select()->toArray();

        $xz_huiyuan_num=array_column($xz_huiyuan_list,null,'shop_id');

        // echo "<pre>";
        // print_r($cj_list);
        

        // $pay_huiyuan_list = Db::name('shop_refund_order')->where([['shop_id', 'in', $shop_list], ['join_time', 'between', "$start_time,$end_time"]])->field('count(id) as sum,shop_id')->group('shop_id')->select()->toArray();

        // 白玉兰会员数
        $xzw_huiyuan_list=Db::name('customer')->where([['shop_id', 'in', $shop_list], ['join_time', 'between', "$start_time,$end_time"], ['membership_leve', '=', "1"]])->field('count(id) as sum,shop_id')->group('shop_id')->select()->toArray();

        $xzw_huiyuan_num=array_column($xzw_huiyuan_list,null,'shop_id');


        // 紫玉兰会员数
        $xzp_huiyuan_list=Db::name('customer')->where([['shop_id', 'in', $shop_list], ['join_time', 'between', "$start_time,$end_time"], ['membership_leve', '=', "2"]])->field('count(id) as sum,shop_id')->group('shop_id')->select()->toArray();

        $xzp_huiyuan_num=array_column($xzp_huiyuan_list,null,'shop_id');


        // 金玉兰会员数
        $xzg_huiyuan_list=Db::name('customer')->where([['shop_id', 'in', $shop_list], ['join_time', 'between', "$start_time,$end_time"], ['membership_leve', '=', "3"]])->field('count(id) as sum,shop_id')->group('shop_id')->select()->toArray();

        $xzg_huiyuan_num=array_column($xzg_huiyuan_list,null,'shop_id');


        // 门店顾客实收

        // 收款单
        $pay_all_list=Db::name('shop_refund_order')->where([['shop_id', 'in', $shop_list], ['deal_time', 'between', "$start_time,$end_time"],['status','=',2],['refund_type','=','101.02'],['type','=',3]])->field('sum(pay_amount) as pay_amount,shop_id')->group('shop_id')->select()->toArray();

        $pay_all=array_column($pay_all_list,null,'shop_id');


        // 退款单
        $pay_all_t_list=Db::name('shop_refund_order')->where([['shop_id', 'in', $shop_list], ['deal_time', 'between', "$start_time,$end_time"],['status','=',2],['refund_type','=','101.02'],['type','=',1]])->field('sum(pay_amount) as pay_amount,shop_id')->group('shop_id')->select()->toArray();

        $pay_all_t=array_column($pay_all_t_list,null,'shop_id');


        // 会员顾客列表
        $xz_vip_list=Db::name('customer')->where([['shop_id', 'in', $shop_list], ['membership_leve', 'in', [1,2,3]]])->group('shop_id')->column('id','shop_id');

        $xz_vip_list=array_column($xz_vip_list,null,'shop_id');

        // echo "<pre>";
        // print_r($xz_vip_list);
        // echo "</pre>";
        // exit;

        $list = [];
        

        foreach ($shop_list as $k => $v) {

            // 会员顾客列表
            $xz_vip_list_all=Db::name('customer')->where([['shop_id', '=', $v], ['membership_leve', 'in', [1,2,3]]])->column('id');

            $xz_vip_list_w=Db::name('customer')->where([['shop_id', '=', $v], ['membership_leve', 'in', [1]]])->column('id');

            $xz_vip_list_p=Db::name('customer')->where([['shop_id', '=', $v], ['membership_leve', 'in', [2]]])->column('id');

            $xz_vip_list_g=Db::name('customer')->where([['shop_id', '=', $v], ['membership_leve', 'in', [3]]])->column('id');

            // 会员顾客当月消费
            // 收款单
            $vip_pay_all=Db::name('shop_refund_order')->where([['uid', 'in', $xz_vip_list_all], ['deal_time', 'between', "$start_time,$end_time"],['status','=',2],['refund_type','=','101.02'],['type','=',3]])->column('sum(pay_amount) as pay_amount');

    
            // 退款单
            $vip_pay_all_t=Db::name('shop_refund_order')->where([['uid', 'in', $xz_vip_list_all], ['deal_time', 'between', "$start_time,$end_time"],['status','=',2],['refund_type','=','101.02'],['type','=',1]])->column('sum(pay_amount) as pay_amount');


            // 白玉兰会员当月消费
            // 收款单
            $wvip_pay_all=Db::name('shop_refund_order')->where([['uid', 'in', $xz_vip_list_w], ['deal_time', 'between', "$start_time,$end_time"],['status','=',2],['refund_type','=','101.02'],['type','=',3]])->column('sum(pay_amount) as pay_amount');

    
            // 退款单
            $wvip_pay_all_t=Db::name('shop_refund_order')->where([['uid', 'in', $xz_vip_list_w], ['deal_time', 'between', "$start_time,$end_time"],['status','=',2],['refund_type','=','101.02'],['type','=',1]])->column('sum(pay_amount) as pay_amount');



            // 紫玉兰会员当月消费
            // 收款单
            $pvip_pay_all=Db::name('shop_refund_order')->where([['uid', 'in', $xz_vip_list_p], ['deal_time', 'between', "$start_time,$end_time"],['status','=',2],['refund_type','=','101.02'],['type','=',3]])->column('sum(pay_amount) as pay_amount');

    
            // 退款单
            $pvip_pay_all_t=Db::name('shop_refund_order')->where([['uid', 'in', $xz_vip_list_p], ['deal_time', 'between', "$start_time,$end_time"],['status','=',2],['refund_type','=','101.02'],['type','=',1]])->column('sum(pay_amount) as pay_amount');



            // 金玉兰会员当月消费
            // 收款单
            $gvip_pay_all=Db::name('shop_refund_order')->where([['uid', 'in', $xz_vip_list_g], ['deal_time', 'between', "$start_time,$end_time"],['status','=',2],['refund_type','=','101.02'],['type','=',3]])->column('sum(pay_amount) as pay_amount');

    
            // 退款单
            $gvip_pay_all_t=Db::name('shop_refund_order')->where([['uid', 'in', $xz_vip_list_g], ['deal_time', 'between', "$start_time,$end_time"],['status','=',2],['refund_type','=','101.02'],['type','=',1]])->column('sum(pay_amount) as pay_amount');
            
            // echo "<pre>";
            // print_r($vip_pay_all);
            // echo "</pre>";
            // exit;

            $one = [];

            $one['shop_market'] = $market_names[$v] ?? '';

            $one['shop_name'] = $shop_names[$v] ?? '';

            $one['shop_id'] = $v ?? '';

            $one['cj_num'] = $cj_num[$v]['sum'] ?? '0';

            $one['xz_huiyuan_num'] = $xz_huiyuan_num[$v]['sum'] ?? '0'; // 新增会员总数

            $one['xzw_huiyuan_num'] = $xzw_huiyuan_num[$v]['sum'] ?? '0'; // 新增白玉兰会员

            $one['xzp_huiyuan_num'] = $xzp_huiyuan_num[$v]['sum'] ?? '0'; // 新增紫玉兰会员

            $one['xzg_huiyuan_num'] = $xzg_huiyuan_num[$v]['sum'] ?? '0'; // 新增金玉兰会员

            $one['pay_all'] = bcsub(($pay_all[$v]['pay_amount'] ?? '0.00') , ($pay_all_t[$v]['pay_amount'] ?? '0.00') , 2) ; // 顾客消费总数

            $one['vip_pay_all'] = bcsub(($vip_pay_all[0] ?? '0.00') , ($vip_pay_all_t[0] ?? '0.00') , 2) ; // 会员顾客消费总数

            $one['wvip_pay_all'] = bcsub(($wvip_pay_all[0] ?? '0.00') , ($wvip_pay_all_t[0] ?? '0.00') , 2) ; // 白玉兰会员顾客消费总数

            $one['pvip_pay_all'] = bcsub(($pvip_pay_all[0] ?? '0.00') , ($pvip_pay_all_t[0] ?? '0.00') , 2) ; // 紫玉兰会员顾客消费总数

            $one['gvip_pay_all'] = bcsub(($gvip_pay_all[0] ?? '0.00') , ($gvip_pay_all_t[0] ?? '0.00') , 2) ; // 金玉兰会员顾客消费总数

            $one['ruhuilv'] = $one['cj_num']==0? '0%' :bcmul(bcdiv($one['xz_huiyuan_num'],$one['cj_num'],10),100,2).'%'; // 入会率
            // $one['ruhuilv'] = '0%';

            $one['huiyuan_gongxiandu'] = $one['pay_all']=='0.00'? '0%' :bcmul(bcdiv($one['vip_pay_all'],$one['pay_all'],10),100,2).'%'; // 会员贡献度

            $one['huiyuan_gongxiandu_w'] = $one['pay_all']=='0.00'? '0%' :bcmul(bcdiv($one['wvip_pay_all'],$one['pay_all'],10),100,2).'%'; // 白玉兰会员贡献度

            $one['huiyuan_gongxiandu_p'] = $one['pay_all']=='0.00'? '0%' :bcmul(bcdiv($one['pvip_pay_all'],$one['pay_all'],10),100,2).'%'; // 紫玉兰会员贡献度

            $one['huiyuan_gongxiandu_g'] = $one['pay_all']=='0.00'? '0%' :bcmul(bcdiv($one['gvip_pay_all'],$one['pay_all'],10),100,2).'%'; // 金玉兰会员贡献度

            array_push($list,$one);
        }

        // echo "<pre>";
        // print_r($list);
        // echo "</pre>";
        // exit;

        return $list;
        // return resultArray(['data' => $list]);
        
    }

    /**
     * 预备会员详情
     */
    public function getYBVipList($request)
    {

        $sTime = strtotime('2022-01-01');
        
        $where = [];

        $paging['page']  = isset($request['page']) && intval($request['page']) ? intval($request['page']) : 1;
        $paging['limit'] = isset($request['limit']) && intval($request['limit']) ? intval($request['limit']) : 10;

        $start_time = strtotime($request['start_time']);
        $end_time = strtotime($request['end_time']);
        
        array_push($where,['shop_id','=',$request['shop_id']]);
        array_push($where,['final_consumption','>=',$start_time]);
        array_push($where,['final_consumption','<=',$end_time]);
        array_push($where,['member_classification','in',[71,72,73]]);
        array_push($where,['membership_leve','=',0]);
        array_push($where,['consumption','>=','5000']);
        array_push($where,['create_time','>=',$sTime]);

        $dataCounts = Db::name('customer')->where($where)->count();

        $customer_list = Db::name('customer')
        ->where($where)
        ->limit(($paging['page'] - 1) * $paging['limit'], $paging['limit'])
        ->column('id,customer_name,telephone,shop_id,nurse_id,member_classification,consumption,final_consumption');

        // echo "<pre>";
        // print_r($customer_list);

        $customer_ids = array_values(array_unique(array_column($customer_list,'id')));

        $where = [];

        array_push($where,['customer_id','in',$customer_ids]);

        $scarList = Db::name('customer_scar')->where($where)->column('customer_setting_id_type','customer_id'); 

        $where = [];

        array_push($where,['id','in',$scarList]);

        $scar_names = Db::name('setting')->where($where)->column('name','id'); 

        // echo "<pre>";
        // print_r($scarList);
        // exit;

        $shop_ids = array_values(array_unique(array_column($customer_list,'shop_id')));

		$nurse_id = array_values(array_unique(array_column($customer_list,'nurse_id')));
        
        $where = [];

        array_push($where,['shop_id','in',$shop_ids]);

        $shopList = Db::name('shop')->where($where)->column('shop_name','shop_id');


        $where = [];

        array_push($where,['id','in',$nurse_id]);

        $nurseList = Db::name('shop_person')->where($where)->column('name','id');

        // 性别数组
        $customer_type = [71=>'C类客户',72=>'B类客户',73=>'A类客户'];


        $list = [];

        foreach($customer_list as $key => $val){
            $one = [];

            $one['customer_name'] = $val['customer_name'];

            $one['telephone'] = $val['telephone'];

            $one['shop_name'] = $shopList[$val['shop_id']] ?? '';

            $one['nurse_name'] = $nurseList[$val['nurse_id']] ?? '';

            $one['final_consumption'] = $val['final_consumption'] > 0 ? date('Y-m-d H:i:s',$val['final_consumption']) : '';

            $one['consumption'] = $val['consumption'];

            $one['member_classification'] = $customer_type[$val['member_classification']];

            $scarID = $scarList[$val['id']] ?? '';

            $one['scar_name'] = $scar_names[$scarID] ?? '';


            array_push($list,$one);
        }

        

        $data              = [];
        $data['list']      = $list ?? [];
        $data['dataCount'] = $dataCounts ?? 0;

        return $data;
        // echo "<pre>";
        // print_r($list);
        // exit;
        
    }

}